package UiUtil;

import java.io.File;
import java.io.FileInputStream;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Properties;
import java.util.Vector;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;

public class ss {

	/**
	 * 
	 */
	public static File inputFile = null;
	public static FileInputStream fileIStream = null;
	public Workbook workbook = null;
	public Sheet sheet = null;
	public static Row row = null;
	public static Cell cell = null;
	public static String excelvalue = null;
	public static Iterator<Row> rowIterator = null;
	public static Iterator<Cell> cellIterator = null;
	public static int numSheets = 0;
	public int columncount, rowcount;
	public static String columnvalue[][];
	public static Hashtable columnshashtable;
	public static Vector columnVector;
	public Properties mapProperties;

	public ss() {

		columnVector = new Vector();
		mapProperties = new Properties();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @param args
	 *            the command line arguments
	 */

	/*
	 * public static void main(String[] args) {
	 * 
	 * 
	 * 
	 * try { inputFile = new File("E:/source codes/trial/Sampleexcel.xls");
	 * fileIStream = new FileInputStream(inputFile); // Use the WorkbookFactory
	 * create() factory method to get // either an HSSFWorkbook or XSSFWorkbook
	 * depending on // the file type wrapped in the FileInputStream workbook =
	 * WorkbookFactory.create(fileIStream);
	 * 
	 * 
	 * // Lets see what we got, an XSSF or HSSFWorkbook if(workbook instanceof
	 * org.apache.poi.hssf.usermodel.HSSFWorkbook) {
	 * System.out.println("You got an HSSFWorkbook"); } else {
	 * System.out.println("You got an XSSFWorkbook"); } // Get the number of
	 * sheets numSheets = workbook.getNumberOfSheets(); // Iterate through each
	 * sheet // Get the sheet sheet = workbook.getSheet("Sheet1");//
	 * .getSheetAt(i); columnvalue=new String [sheet.getLastRowNum()][2000];
	 * //commented for only specified sheet and calling readcolumnxlsxname ss
	 * s=new ss(); s.readxlsxcolumn(sheet); if(checkxlsxcolumn("requestD")) {
	 * System.out.println("column Exist"); } else {
	 * System.out.println("column Doesn't Exist"); } s.readeachcolumn();
	 * s.displayxlsx();
	 * 
	 * for(int i = 0; i < numSheets; i++) { // Get the sheet sheet =
	 * workbook.getSheet("Sheet1");// .getSheetAt(i);
	 * 
	 * 
	 * // Get an iterator to work through the rows on the sheet rowIterator =
	 * sheet.iterator(); // Iterate through the rows if(rowIterator.hasNext()) {
	 * row = rowIterator.next(); System.out.println("Processing row number: " +
	 * row.getRowNum()); // Get an iterator to work through the cells on therow
	 * cellIterator = row.iterator(); // Iterate through the cells
	 * while(cellIterator.hasNext()) { cell = cellIterator.next(); // Just print
	 * out the num,ber of the cell // and the String representatin of the
	 * instance. if(cell.getCellType()==Cell.CELL_TYPE_STRING)
	 * {excelvalue=cell.getRichStringCellValue().getString();
	 * //System.out.println("Cell: "
	 * +cell.getRichStringCellValue().getString()); }
	 * if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){ if
	 * (DateUtil.isCellDateFormatted(cell)) {
	 * excelvalue=cell.getDateCellValue().toString(); //
	 * System.out.println("Cell: "+cell.getDateCellValue().toString()); } else {
	 * excelvalue=Double.toString(cell.getNumericCellValue());
	 * //System.out.println
	 * ("Cell: "+Double.toString(cell.getNumericCellValue())); } }
	 * if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN)
	 * {excelvalue=Boolean.toString(cell.getBooleanCellValue()); //
	 * System.out.println("Cell: " +cell.getBooleanCellValue());
	 * 
	 * } if(cell.getCellType()==Cell.CELL_TYPE_FORMULA)
	 * {excelvalue=cell.getCellFormula().toString();
	 * //System.out.println("Cell: " +cell.getCellFormula().toString());
	 * 
	 * }
	 * 
	 * System.out.println(excelvalue); } } }
	 * 
	 * } catch(Exception ex) { System.out.println("Caught an: " +
	 * ex.getClass().getName()); System.out.println("Message: " +
	 * ex.getMessage()); System.out.println("Stacktrace follows.........");
	 * ex.printStackTrace(System.out); } finally { if(fileIStream != null) { try
	 * { fileIStream.close(); } catch(Exception ex) { // I G N O R E // } } } }
	 */

	/*
	 * public void displayxlsx() { rowIterator = sheet.iterator(); // Iterate
	 * through the rows while(rowIterator.hasNext()) { int i=0; row =
	 * rowIterator.next(); for( i=0;i<columnshashtable.size();i++){
	 * //System.out.
	 * println(columnVector.elementAt(i)+" = "+columnshashtable.get(
	 * columnVector.elementAt(i)));
	 * System.out.println(columnVector.elementAt(i)+
	 * " = "+columnshashtable.get(columnVector.elementAt(i)));
	 * //System.out.println
	 * ("requesID= "+(String)columnshashtable.get("requestID")); } } }
	 */

	public Hashtable readeachcolumn(Iterator rowIterator) {

		int count = 0;
		columnshashtable = new Hashtable();
		// Get an iterator to work through the rows on the sheet
		// rowIterator = sheet.iterator();
		// Iterate through the rows
		if (rowIterator.hasNext()) {
			int i = 0;
			row = (Row) rowIterator.next();
			System.out.println("Processing row number: " + row.getRowNum());
			// Get an iterator to work through the cells on the row
			cellIterator = row.iterator();
			// Iterate through the cells
			while (cellIterator.hasNext()) {
				cell = cellIterator.next();
				cell.setCellType(1);
				// Just print out the number of the cell
				// and the String representation of the instance.
				if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					excelvalue = cell.getRichStringCellValue().getString();
					// System.out.println("Cell: "
					// +cell.getRichStringCellValue().getString());
				}
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					if (DateUtil.isCellDateFormatted(cell)) {
						excelvalue = cell.getDateCellValue().toString();
						// System.out.println("Cell: "+cell.getDateCellValue().toString());
					} else {
						excelvalue = Double
								.toString(cell.getNumericCellValue());
						// System.out.println("Cell: "+Double.toString(cell.getNumericCellValue()));
					}
				}
				if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					excelvalue = Boolean.toString(cell.getBooleanCellValue());
					// System.out.println("Cell: " +cell.getBooleanCellValue());

				}
				if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
					excelvalue = cell.getCellFormula().toString();
					// System.out.println("Cell: "
					// +cell.getCellFormula().toString());

				}
				System.out.println(excelvalue);
				columnshashtable.put(columnVector.elementAt(i), excelvalue);
				i++;

			}
		}
		return columnshashtable;
	}

	public void readxlsxcolumn(Sheet sheet) {

		// Get an iterator to work through the rows on the sheet
		rowIterator = sheet.iterator();
		rowcount = sheet.getLastRowNum() + 1;
		// Iterate through the rows
		while (rowIterator.hasNext()) {
			row = rowIterator.next();
			// System.out.println("Processing row number: " + row.getRowNum());
			// Get an iterator to work through the cells on the row
			cellIterator = row.iterator();
			// Iterate through the cells
			while (cellIterator.hasNext()) {
				cell = cellIterator.next();
				cell.setCellType(1);
				// Just print out the number of the cell
				// and the String representation of the instance.
				if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					excelvalue = cell.getRichStringCellValue().getString();
					// System.out.println("Cell: "
					// +cell.getRichStringCellValue().getString());
				}
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					if (DateUtil.isCellDateFormatted(cell)) {
						excelvalue = cell.getDateCellValue().toString();
						// System.out.println("Cell: "+cell.getDateCellValue().toString());
					} else {
						excelvalue = Double
								.toString(cell.getNumericCellValue());
						// System.out.println("Cell: "+Double.toString(cell.getNumericCellValue()));
					}
				}
				if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					excelvalue = Boolean.toString(cell.getBooleanCellValue());
					// System.out.println("Cell: " +cell.getBooleanCellValue());

				}
				if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
					excelvalue = cell.getCellFormula().toString();
					// System.out.println("Cell: "
					// +cell.getCellFormula().toString());

				}
				columncount++;
				this.columnVector.add(excelvalue);
				// System.out.println("column "+columncount+"="+excelvalue/*+"\tno="+
				// columnVector.size()+cell.CELL_TYPE_STRING*/);
			}
		}
	}

	public Boolean checkxlsxcolumn(String columnname, Sheet sheet) {

		// Get an iterator to work through the rows on the sheet
		rowIterator = sheet.iterator();
		// Iterate through the rows
		if (rowIterator.hasNext()) {
			row = rowIterator.next();
			// System.out.println("Processing row number: " + row.getRowNum());
			// Get an iterator to work through the cells on the row
			cellIterator = row.iterator();
			// Iterate through the cells
			while (cellIterator.hasNext()) {
				cell = cellIterator.next();
				// Just print out the number of the cell
				// and the String representation of the instance.
				if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					excelvalue = cell.getRichStringCellValue().getString();
					// System.out.println("Cell: "
					// +cell.getRichStringCellValue().getString());
				}
				if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
					if (DateUtil.isCellDateFormatted(cell)) {
						excelvalue = cell.getDateCellValue().toString();
						// System.out.println("Cell: "+cell.getDateCellValue().toString());
					} else {
						excelvalue = Double
								.toString(cell.getNumericCellValue());
						// System.out.println("Cell: "+Double.toString(cell.getNumericCellValue()));
					}
				}
				if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
					excelvalue = Boolean.toString(cell.getBooleanCellValue());
					// System.out.println("Cell: " +cell.getBooleanCellValue());

				}
				if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
					excelvalue = cell.getCellFormula().toString();
					// System.out.println("Cell: "
					// +cell.getCellFormula().toString());

				}
				if (excelvalue.equals(columnname))
					return true;
				// System.out.println(excelvalue);
			}
		}
		return false;
	}

}